package com.eric.demo.jdbc;

import com.alibaba.druid.pool.DruidDataSource;
import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author ZhangGuangyin on 2018/9/4
 */
public class JDBCTest {

    static String url = "jdbc:mysql://127.0.0.1:3306/test";
    static String username = "root";
    static String password = "123456";

    private DataSource dataSource;

    public static void main(String[] argvs) throws Exception {
        JDBCTest test = new JDBCTest();
        test.printTableInfo();
    }

    public JDBCTest() throws Exception {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setUseUnfairLock(true);
        dataSource.setNotFullTimeoutRetryCount(2);
        dataSource.setInitialSize(5);
        dataSource.setMinIdle(5);
        dataSource.setMaxActive(10);
        dataSource.setMaxWait(5);
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.addConnectionProperty("useServerPrepStmts", "false");
        dataSource.addConnectionProperty("rewriteBatchedStatements", "true");
        dataSource.addConnectionProperty("allowMultiQueries", "true");
        dataSource.addConnectionProperty("readOnlyPropagatesToServer", "false");
        dataSource.setValidationQuery("select 1");
        dataSource.setExceptionSorter("com.alibaba.druid.pool.vendor.MySqlExceptionSorter");
        dataSource.setValidConnectionCheckerClassName("com.alibaba.druid.pool.vendor.MySqlValidConnectionChecker");
        this.dataSource = dataSource;
    }

    public void printTableInfo(){
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.execute(new ConnectionCallback<Object>() {

            public Object doInConnection(Connection conn) throws SQLException, DataAccessException{
                DatabaseMetaData metaData = conn.getMetaData();
                String databaseName = metaData.getDatabaseProductName();
                System.out.println("databaseName=" + databaseName);
                System.out.println("databaseName getIdentifierName=" + getIdentifierName(databaseName, metaData));
                System.out.println("conn.getCatalog()=" + conn.getCatalog());

                ResultSet rs1 = metaData.getTables(conn.getCatalog(), "test", null, null);
                System.out.println("print table info--------------------------------");
                List<String> tableList = new ArrayList<>();
                while (rs1.next()) {
                    String catlog = rs1.getString(1);
                    String schema = rs1.getString(2);
                    String name = rs1.getString(3);
                    String type = rs1.getString(4);

                    tableList.add(name);
                    System.out.println("catlog=" + catlog);
                    System.out.println("schema=" + schema);
                    System.out.println("name=" + name);
                    System.out.println("type=" + type);
                    System.out.println("--------------------------------------");
                }
                rs1.close();

                /*ResultSet rs2 = metaData.getColumns("test", "test", "student", null);
                System.out.println("print table column info--------------------------------");
                while (rs2.next()) {
                    String catlog = rs2.getString(1);
                    String schema = rs2.getString(2);
                    String name = rs2.getString(3);
                    String columnName = rs2.getString(4); // COLUMN_NAME
                    int columnType = rs2.getInt(5);
                    String typeName = rs2.getString(6);

                    System.out.println("catlog=" + catlog);
                    System.out.println("schema=" + schema);
                    System.out.println("name=" + name);
                    System.out.println("columnName=" + columnName);
                    System.out.println("columnType=" + columnType);
                    System.out.println("typeName=" + typeName);
                    System.out.println("--------------------------------------");
                }
                rs2.close();

                ResultSet rs3 = metaData.getPrimaryKeys("test", "test", "student");
                System.out.println("print table primary info--------------------------------");
                while (rs3.next()) {
                    String catlog = rs3.getString(1);
                    String schema = rs3.getString(2);
                    String name = rs3.getString(3);
                    String primaryKey = rs3.getString(4); // COLUMN_NAME

                    System.out.println("catlog=" + catlog);
                    System.out.println("schema=" + schema);
                    System.out.println("name=" + name);
                    System.out.println("primaryKey=" + primaryKey);
                    System.out.println("--------------------------------------");
                }
                rs3.close();
*/
                return null;
            }
        });
    }

    private static String getIdentifierName(String name, DatabaseMetaData metaData) throws SQLException {
        if (metaData.storesMixedCaseIdentifiers()) {
            return name; // 保留原始名
        } else if (metaData.storesUpperCaseIdentifiers()) {
            return StringUtils.upperCase(name);
        } else if (metaData.storesLowerCaseIdentifiers()) {
            return StringUtils.lowerCase(name);
        } else {
            return name;
        }
    }
}
